package com.example.Dao;

import com.example.pojo.News;
import com.example.utils.ConnUtils;
import com.example.utils.DataUtils;

import javax.xml.crypto.Data;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class NewsDao {
    //管理员角度
    //统计总行数
    public int getTotal(){
        int flag =0;
        Connection conn =ConnUtils.getConn();
        //编写SQL
        String sql ="select count(*) from news";
        try {
            PreparedStatement pst =conn.prepareStatement(sql);
            ResultSet rs = pst.executeQuery();
            if (rs.next()){
                flag = rs.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DataUtils.closeConnection(conn);
        }
        return flag;
    }
    public  List<News> getAllNews(int currentPage,int pageSize){
        List<News> list =new ArrayList<>();
        Connection conn = DataUtils.getConn();
        String sql ="SELECT * FROM news limit ?,?";
        try {
            PreparedStatement pst= conn.prepareStatement(sql);
            //执行
            pst.setInt(1,(currentPage-1)*pageSize);
            pst.setInt(2,pageSize);
            ResultSet rs = pst.executeQuery();
            //封装
            while (rs.next()){
                News news =new News(rs.getInt(1),rs.getString(2),
                        rs.getString(3),rs.getString(4),
                        rs.getString(5),rs.getString(6),
                        rs.getString(7),rs.getInt(8));
                list.add(news);
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DataUtils.closeConnection(conn);
        }
        return list;
    }
    public  List<News> getTrendingNews(){
        List<News> list =new ArrayList<>();
        Connection conn = DataUtils.getConn();
        String sql ="SELECT * FROM news  ORDER BY newsViews DESC limit 0,10";
        try {
            PreparedStatement pst= conn.prepareStatement(sql);
            //执行
            ResultSet rs = pst.executeQuery();
            //封装
            while (rs.next()){
                News news =new News(rs.getInt(1),rs.getString(2),
                        rs.getString(3),rs.getString(4),
                        rs.getString(5),rs.getString(6),
                        rs.getString(7),rs.getInt(8));
                list.add(news);
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DataUtils.closeConnection(conn);
        }
        return list;
    }
    //添加新闻，删除新闻，更新新闻
    public int addNews(News news){
        int flag=0;
        Connection conn= DataUtils.getConn();
        String sql="insert into news(newsType,newsTitle,newsContent,newsImgs,newsAuthor,publishDate,newsState)" +
                " values(?,?,?,?,?,SYSDATE(),?)";
        try {
            PreparedStatement pst =conn.prepareStatement(sql);
            pst.setString(1,news.getNewsType());
            pst.setString(2,news.getNewsTitle());
            pst.setString(3,news.getNewsContent());
            pst.setString(4,news.getNewsImgs());
            pst.setString(5,news.getNewsAuthor());
            pst.setInt(6,news.getNewsState());
            flag = pst.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return  flag;
    }
    public int updateNews(News news){
        int flag=0;
        Connection conn= DataUtils.getConn();
        String sql="update news set newsType=?,newsTitle=?,newsContent=?,newsImgs=?,newsAuthor=?,publishDate=?,newsState=? where newsId=?";
        try {
            PreparedStatement pst =conn.prepareStatement(sql);
            pst.setString(1,news.getNewsType());
            pst.setString(2,news.getNewsTitle());
            pst.setString(3,news.getNewsContent());
            pst.setString(4,news.getNewsImgs());
            pst.setString(5,news.getNewsAuthor());
            pst.setString(6,news.getPublishDate());
            pst.setInt(7,news.getNewsState());
            pst.setInt(8,news.getNewsId());
            flag = pst.executeUpdate();
            return flag;
        }catch (SQLException e){
            throw new RuntimeException(e);
        }finally {
            DataUtils.closeConnection(conn);
        }
    }
    public int deleteNews(int id){
        int flag=0;
        Connection conn= DataUtils.getConn();
        String sql="delete from news where newsId=?";
        try {
            PreparedStatement pst =conn.prepareStatement(sql);
            pst.setInt(1,id);
            flag = pst.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        finally {
            DataUtils.closeConnection(conn);
        }
        return  flag;
    }
    public  int deleteBatchNews(List<Integer> ids){
        int flag=0;
        //关掉自动提交
        Connection conn = DataUtils.getConn();
        try {
            conn.setAutoCommit(false);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        String sql = "delete from news where newsId in ";
        try {
            StringBuilder sb =new StringBuilder();
            for (int i = 0; i < ids.size(); i++) {
                if (i==0){
                    sb.append("(");
                }
                sb.append("?");
                if (i<ids.size()-1){
                    sb.append(",");
                }
                if (i==ids.size()-1){
                    sb.append(")");

                }
            }
            sql +=sb.toString();
            PreparedStatement pst =conn.prepareStatement(sql);
            for (int i = 0; i <ids.size() ; i++) {
                pst.setInt(i+1,ids.get(i));
            }
            System.out.println(sql);
            flag = pst.executeUpdate();
            if (flag>0){
                conn.commit();
            }
        }catch (Exception e){
            //回滚
            try {
                conn.rollback();
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
        }finally {
            DataUtils.closeConnection(conn);
        }
        return flag;
    }
public  List<News> getNewByType(String type){
        List<News> list =new ArrayList<>();
        Connection conn = DataUtils.getConn();
        String sql ="SELECT * FROM news WHERE newsType=?";
        try {
            PreparedStatement pst= conn.prepareStatement(sql);
            pst.setString(1,type);
            ResultSet rs = pst.executeQuery();
            while (rs.next()){
                News news =new News(rs.getInt(1),rs.getString(2),
                        rs.getString(3),rs.getString(4),
                        rs.getString(5),rs.getString(6),
                        rs.getString(7),rs.getInt(8));
                list.add(news);
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    return list;
}
public   News  getNewsInfo(int Id){
        Connection conn =DataUtils.getConn();
        String sql ="SELECT * FROM news WHERE newsId=?";
    String sql1 ="update news set newsViews = newsViews +1 where newsId =?";
    News news =null;
        try {
            PreparedStatement pst= conn.prepareStatement(sql);
            pst.setInt(1,Id);
            ResultSet rs = pst.executeQuery();
            PreparedStatement pst1= conn.prepareStatement(sql1);
            pst1.setInt(1,Id);
            pst1.executeUpdate();
           if (rs.next()){
               news =new News(rs.getInt(1),rs.getString(2),
                       rs.getString(3),rs.getString(4),
                       rs.getString(5),rs.getString(6),
                       rs.getString(7),rs.getInt(8));
           }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {

            DataUtils.closeConnection(conn);
        }
        return news;

}


}
